-- SELECT TOP 10 * FROM SERVER16.[VPB_WHR2].DBO.MD_RPT_DAILY_VPB -- MATURITY_DATE (BAO LANH)
   --SELECT TOP 100 * FROM SERVER16.[VPB_WHR2].DBO.MD_RPT_DAILY_VPB

DECLARE @DATE_UPDATE NVARCHAR(100)= (SELECT [DATE_UPDATE] FROM [DATE_EM_REPORT])
DECLARE @DATE_LAST_MONTH NVARCHAR(100)= (SELECT DATEADD(M,2,CONVERT(DATE,@DATE_UPDATE)) )

IF EXISTS(SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'BL_EXPIRY_RAW')
        DROP TABLE BL_EXPIRY_RAW

IF EXISTS(SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'BL_CIF')
        DROP TABLE BL_CIF
		
		select distinct cust_id into BL_CIF from SERVER12.TRANGLT9.[DBO].[DOANH_SO_BAO_LANH]

   SELECT ROW_NUMBER() OVER(PARTITION BY B.MD_ID ORDER BY B.BUSINESS_DATE DESC) AS RN,  B.* 
   INTO BL_EXPIRY_RAW
   FROM  SERVER16.[VPB_WHR2].DBO.MD_RPT_DAILY_VPB B
   WHERE B.CUST_ID IN (SELECT cust_id FROM BL_CIF) AND B.MATURITY_DATE BETWEEN @DATE_UPDATE AND @DATE_LAST_MONTH

   
IF EXISTS(SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'BL_EXPIRY_TRANS')
        DROP TABLE BL_EXPIRY_TRANS

   SELECT * INTO BL_EXPIRY_TRANS FROM BL_EXPIRY_RAW WHERE RN=1

   --SELECT * FROM BL_EXPIRY_TRANS
     
IF EXISTS(SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'TBL_CM_ALERT_BL')
        DROP TABLE TBL_CM_ALERT_BL

   SELECT A.BUSINESS_DATE,MD_ID, MATURITY_DATE, VALUE_DATE, CO_CODE AS BRANCH_ID, B.BRANCH_CODE_SME, B.BRANCH_NAME_SME, CUST_ID AS CIF, A.DAO AS DAO_TRANS, D.DAO_NAME AS DAO_TRANS_NAME, C.DAO AS DAO_CIF,  C.DAO_NAME , C.CUS_NAME, C.INDUSTRY_NAME, C.INDUSTRY_NAME_EN, B.REGION
  INTO TBL_CM_ALERT_BL  
  FROM BL_EXPIRY_TRANS A, TBL_BRANCH B, TBL_CUSTOMER C, TBL_HR D
  WHERE A.CUST_ID = C.CIF AND B.BRANCH_ID=A.CO_CODE AND A.DAO = D.DAO
  --WHERE EXPIRY_DATE < '20160926'
  ORDER BY A.DAO


  
update b
set b.date_data = a.BUSINESS_DATE
from (select max(business_date) as BUSINESS_DATE from TBL_CM_ALERT_BL) a,  TBL_DATE_BUSINESS b
where b.name_table = 'TBL_CM_ALERT_BL'
